

ALTER  proc sp_load_final_inventory_by_line_item_id
@inputLineItemId int
As
declare @process_step_id bigint
declare @process_type varchar(20)
declare @process_type_name varchar(20)

declare @results Table(Part_Number varchar (50),
			Lot_Number varchar(20),
			Lot_ID bigint, 
			Location_ID int,
			Location varchar(20),
			Step_Name varchar(20),
			Date_In datetime,
			Qty_In int,
			--Expected_Date_Out datetime,
			Balance_Qty int)

declare @temp_lot_id int
declare @temp_process_step_id bigint
declare @temp_location_id int
declare @temp_lot_number varchar(20)
declare @temp_location varchar(20)
declare @temp_step_name varchar(20)
declare @temp_date_in datetime
declare @temp_qty_in int
declare @temp_ship_qty int
declare @temp_expected_date_out datetime
declare @temp_expected_qty_out int
declare @temp_yield float

declare @tempCursor CURSOR
declare @pfcursor_deallocate_status int
declare @partNumber varchar(50)
set @pfcursor_deallocate_status = 0

declare @marketingPartNumber varchar(50)
declare @inputProductId int
declare @inputProcessStepId int
select @inputProductId = product_id from customer_open_order_lineitem where lineItem_Id = @inputLineItemId

declare processStepIdCursor CURSOR for
select processStep_Id from kit_table where product_id = @inputProductId

open processStepIdCursor
	fetch next from processStepIdCursor
	into @inputProcessStepId

	while @@fetch_status = 0
	begin

		select @marketingPartNumber = part_number from product where processStep_id = @inputProcessStepId

		select @temp_process_step_id = ProcessStep_ID from Product where P_Marketing_Part_Number =  @marketingPartNumber
		declare pfCursor CURSOR for
		select *from fn_get_process_flow_by_id(@temp_process_step_id)
		
		open pfCursor
			fetch next from pfCursor
			into @process_step_id, @process_type, @process_type_name
		
			while @@fetch_status = 0
			begin
				print '@Process_type_name = '+ @process_type_name
				if @process_type_name = 'Finished Goods'
				begin
					set @tempCursor = CURSOR for
					select Lot_ID, Lot_Number, Location_ID from Lot_Table where ProcessStep_ID = @process_step_id and Lot_Current_Status = 'Running'
					
					open @tempCursor
					fetch next from @tempCursor
					into @temp_lot_id, @temp_lot_number, @temp_location_id
		
					while @@fetch_status = 0
					begin
						select @temp_qty_in = IL_Qty_In, @temp_date_in=IL_Date_In, @temp_ship_qty=IL_Qty_Balance from Inventory_Lot where Lot_ID = @temp_lot_id
						--select @temp_ship_qty = sum(CSLNT_Qty_Pull) from Customer_Shipping_Lot_Number_Table where Lot_ID = @temp_lot_id
						if @temp_ship_qty = 0 or @temp_ship_qty is null
						begin
							--print 'Qty_In = '
							--print @temp_qty_in
							--print 'Balance = '
							--print @temp_ship_qty
							set @temp_ship_qty = @temp_qty_in
						end
						--set @temp_qty_in = (@temp_qty_in - @temp_ship_qty)*/
						--set @temp_expected_qty_out =0
						-- NOTE: expected date out should be calculated .....currently I am assigning to the date in
						--select @temp_expected_date_out = @temp_date_in
						-- select @temp_expected_date_out = dateadd(day,(select IST_Max_Time from Inventory_Step_Table where ProcessStep_ID =@process_step_id) ,@temp_date_in)
						select @temp_location = (select v.V_Name + '/'+ a.City from Location l, Vendor v, Address a where l.Location_ID = @temp_location_id and l.Vendor_ID = v.Vendor_ID and l.Address_ID = a.Address_ID)
						insert into @results(Part_Number, Lot_Number, Lot_ID, Location_ID, Location, Step_Name, Date_In, Qty_In, Balance_Qty) values(@marketingPartNumber, @temp_lot_number, @temp_lot_id, @temp_location_id, @temp_location, @process_type_name, @temp_date_in, @temp_qty_in, @temp_ship_qty)
		
						fetch next from @tempCursor
						into @temp_lot_id, @temp_lot_number, @temp_location_id
					end
					close @tempCursor
					deallocate @tempCursor
					set @pfcursor_deallocate_status = 1
				end
						/*else if @process_type_name = 'Mark & Pack'
						begin
							set @tempCursor = CURSOR for
					select Lot_ID, Lot_Number, Location_ID from Lot_Table where ProcessStep_ID = @process_step_id and Lot_Current_Status = 'Running'
					
					open @tempCursor
					fetch next from @tempCursor
					into @temp_lot_id, @temp_lot_number, @temp_location_id
		
					while @@fetch_status = 0
					begin
						select @temp_qty_in = IL_Qty_In, @temp_date_in=IL_Date_In from Inventory_Lot where Lot_ID = @temp_lot_id
		
						select @temp_yield = (select MPT_Target_Yield from Mark_Pack_Table where ProcessStep_ID = @process_step_id)
						select @temp_expected_qty_out = convert(int, @temp_qty_in * @temp_yield)
		
						select @temp_expected_date_out = dateadd(day,(select PSLT_Cycle_Time_Normal from Process_Step_Location_Table where ProcessStep_ID =@process_step_id and Location_ID = @temp_location_id) ,@temp_date_in)
						select @temp_location = (select v.V_Name + '/'+ a.City from Location l, Vendor v, Address a where l.Location_ID = @temp_location_id and l.Vendor_ID = v.Vendor_ID and l.Address_ID = a.Address_ID)
						insert into @results(Lot_Number, Lot_ID, Location_ID, Location, Step_Name, Date_In, Qty_In, Expected_Date_Out, Expected_Qty_Out) values(@temp_lot_number, @temp_lot_id, @temp_location_id, @temp_location, @process_type_name, @temp_date_in, @temp_qty_in, @temp_expected_date_out, @temp_expected_qty_out)
		
						fetch next from @tempCursor
						into @temp_lot_id, @temp_lot_number, @temp_location_id
					end
					close @tempCursor
					deallocate @tempCursor
		
				end
				else if @process_type_name = 'Class Store'
				begin
					set @tempCursor = CURSOR for
					select Lot_ID, Lot_Number, Location_ID from Lot_Table where ProcessStep_ID = @process_step_id and Lot_Current_Status = 'Running'
					
					open @tempCursor
					fetch next from @tempCursor
					into @temp_lot_id, @temp_lot_number, @temp_location_id
		
					while @@fetch_status = 0
					begin
						select @temp_qty_in = IL_Qty_In, @temp_date_in=IL_Date_In from Inventory_Lot where Lot_ID = @temp_lot_id
						select @temp_expected_qty_out = @temp_qty_in
						-- NOTE: expected date out should be calculated .....currently I am assigning to the date in
						select @temp_expected_date_out = @temp_date_in
						-- select @temp_expected_date_out = dateadd(day,(select IST_Max_Time from Inventory_Step_Table where ProcessStep_ID =@process_step_id) ,@temp_date_in)
						select @temp_location = (select v.V_Name + '/'+ a.City from Location l, Vendor v, Address a where l.Location_ID = @temp_location_id and l.Vendor_ID = v.Vendor_ID and l.Address_ID = a.Address_ID)
						insert into @results(Lot_Number, Lot_ID, Location_ID, Location, Step_Name, Date_In, Qty_In, Expected_Date_Out, Expected_Qty_Out) values(@temp_lot_number, @temp_lot_id, @temp_location_id, @temp_location, @process_type_name, @temp_date_in, @temp_qty_in, @temp_expected_date_out, @temp_expected_qty_out)
		
						fetch next from @tempCursor
						into @temp_lot_id, @temp_lot_number, @temp_location_id
					end
					close @tempCursor
					deallocate @tempCursor
		
				end
				else if @process_type_name = 'TEST'
				begin
					set @tempCursor = CURSOR for
					select Lot_ID, Lot_Number, Location_ID from Lot_Table where ProcessStep_ID = @process_step_id and Lot_Current_Status = 'Running'
					
					open @tempCursor
					fetch next from @tempCursor
					into @temp_lot_id, @temp_lot_number, @temp_location_id
		
					while @@fetch_status = 0
					begin
						select @temp_qty_in = FTL_Qty_In, @temp_date_in=FTL_Date_In from Final_Test_Lot where Lot_ID = @temp_lot_id
		
						select @temp_yield = (select FTT_Target_Yield from Final_Test_Table where ProcessStep_ID = @process_step_id)
						select @temp_expected_qty_out = convert(int, (@temp_qty_in * @temp_yield)/100)
		
						select @temp_expected_date_out = dateadd(day,(select PSLT_Cycle_Time_Normal from Process_Step_Location_Table where ProcessStep_ID =@process_step_id and Location_ID = @temp_location_id) ,@temp_date_in)
						select @temp_location = (select v.V_Name + '/'+ a.City from Location l, Vendor v, Address a where l.Location_ID = @temp_location_id and l.Vendor_ID = v.Vendor_ID and l.Address_ID = a.Address_ID)
						insert into @results(Lot_Number, Lot_ID, Location_ID, Location, Step_Name, Date_In, Qty_In, Expected_Date_Out, Expected_Qty_Out) values(@temp_lot_number, @temp_lot_id, @temp_location_id, @temp_location, @process_type_name, @temp_date_in, @temp_qty_in, @temp_expected_date_out, @temp_expected_qty_out)
		
						fetch next from @tempCursor
						into @temp_lot_id, @temp_lot_number, @temp_location_id
					end
					close @tempCursor
					deallocate @tempCursor
		
					select @pfcursor_deallocate_status = 1
				end*/
				/*else
				begin
					if @pfcursor_deallocate_status = 1
					begin
						--deallocate @tempCursor
						
						close pfCursor
						deallocate pfCursor
						select *from @results
						return
					end
				end*/
				
				fetch next from pfCursor
				into @process_step_id, @process_type, @process_type_name
			end
		
		close pfCursor
		deallocate pfCursor
	
	
	fetch next from processStepIdCursor
	into @inputProcessStepId
	end


close processStepIdCursor
deallocate processStepIdCursor

select *from @results					
return

